﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;

namespace WindowsFormsApplication1
{
    class Model
    {
        private static Model instance = null;
        private string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=TapuhiDB.accdb;Persist Security Info=False;";
        private OleDbConnection MyConn;
        
        private Model()
        {
            MyConn = new OleDbConnection(ConnStr);
            MyConn.Open();
        }

        public static Model GetInstance()
        {
            if (instance == null)
                instance = new Model();
            return instance;
        }

        public void InsertProduct(int prodCode)
        {
            string query = "INSERT INTO Products VALUES(" + prodCode + ", 'test', 'telma', 123, 421, 10)";
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            com.ExecuteNonQuery();

            com.Dispose();
        }

        public void InsertShopping(int ID, int prodCode, int amount, double cost)
        {
            string query = "INSERT INTO Shopping VALUES(" + ID + "," + prodCode + ",#" + DateTime.Now + "#," + amount + "," + cost + ")";
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            com.ExecuteNonQuery();

            com.Dispose();
        }

        public void UpdateProductPrice(int prodCode, double newPrice)
        {
            string query = "UPDATE Products SET Price = " + newPrice + " WHERE ProdCode = " + prodCode;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            com.ExecuteNonQuery();

            com.Dispose();
        }

        public void UpdateProductStock(int prodCode, int amount)
        {
            string query = "UPDATE Products SET Amount = Amount -" + amount + " WHERE ProdCode = " + prodCode;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            com.ExecuteNonQuery();

            com.Dispose();
        }

        public void UpdateCustDebt(int ID, double sum)
        {
            string query = "UPDATE Customers SET Debt = Debt +" + sum + " WHERE ID = " + ID;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            com.ExecuteNonQuery();

            com.Dispose();
        }

        public double GetProductPrice(int prodCode)
        {
            double price;
            string query = "SELECT Price FROM Products WHERE ProdCode = " + prodCode;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            OleDbDataReader reader = com.ExecuteReader();
            reader.Read();

            price = reader.GetDouble(0);

            com.Dispose();

            return price;
        }

        public double GetProductCode(int orderNum)
        {
            int code;
            string query = "SELECT ProdCode FROM Orders WHERE ID = " + orderNum;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            OleDbDataReader reader = com.ExecuteReader();
            reader.Read();

            code = reader.GetInt32(0);

            com.Dispose();

            return code;
        }

        public double GetAmount(int orderNum)
        {
            int amount;
            string query = "SELECT Amount FROM Orders WHERE ID = " + orderNum;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            OleDbDataReader reader = com.ExecuteReader();
            reader.Read();

            amount = reader.GetInt32(0);

            com.Dispose();

            return amount;
        }

        public void InsertToStock(int prodCode, int orderNum, int amount, DateTime recieved, DateTime expired)
        {
            string query = "INSERT INTO ProdInStock VALUES(" + prodCode + "," + orderNum+ "," + amount + ",#" + recieved + "#,#" +  expired +"#)";
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            com.ExecuteNonQuery();

            com.Dispose();
            string query2 = "UPDATE Orders SET Recieved = " + 1 + " WHERE ID = " + orderNum;
            OleDbCommand com2 = new OleDbCommand(query2, MyConn);

            com2.CommandType = CommandType.Text;
            com2.ExecuteNonQuery();

            com2.Dispose();
            

            string query3 = "UPDATE Products SET Amount = Amount +" + amount + " WHERE ProdCode = " + prodCode;
            OleDbCommand com3 = new OleDbCommand(query3, MyConn);
            com3.CommandType = CommandType.Text;
            com3.ExecuteNonQuery();

            com3.Dispose();
        }

        public string GetNameByID(int ID)
        {
            string name;
            string query = "SELECT FirstName, LastName FROM Customers WHERE ID = " + ID;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            OleDbDataReader reader = com.ExecuteReader();
            reader.Read();

            name = reader.GetString(0) + " " + reader.GetString(1);

            com.Dispose();

            return name;
        }

        public string GetNameByCode(int code)
        {
            string name;
            string query = "SELECT ProdName FROM Products WHERE ProdCode = " + code;
            OleDbCommand com = new OleDbCommand(query, MyConn);

            com.CommandType = CommandType.Text;
            OleDbDataReader reader = com.ExecuteReader();
            reader.Read();

            name = reader.GetString(0);

            com.Dispose();

            return name;
        }
    }
}
